package com.example.repository;

import com.example.model.Income;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface IncomeRepository extends JpaRepository<Income, Long> {
    
    // 根据类型查找收入
    Page<Income> findByType(Income.IncomeType type, Pageable pageable);
    
    // 根据分类查找收入
    Page<Income> findByCategory(Income.IncomeCategory category, Pageable pageable);
    
    // 获取总收入
    @Query("SELECT COALESCE(SUM(i.amount), 0) FROM Income i")
    BigDecimal getTotalIncome();
    
    // 获取今日收入
    @Query("SELECT COALESCE(SUM(i.amount), 0) FROM Income i WHERE i.incomeDate >= :startOfDay AND i.incomeDate < :endOfDay")
    BigDecimal getTodayIncome(@Param("startOfDay") LocalDateTime startOfDay, @Param("endOfDay") LocalDateTime endOfDay);
    
    // 获取本月收入
    @Query("SELECT COALESCE(SUM(i.amount), 0) FROM Income i WHERE YEAR(i.incomeDate) = YEAR(CURRENT_DATE) AND MONTH(i.incomeDate) = MONTH(CURRENT_DATE)")
    BigDecimal getMonthlyIncome();
    
    // 获取本年收入
    @Query("SELECT COALESCE(SUM(i.amount), 0) FROM Income i WHERE YEAR(i.incomeDate) = YEAR(CURRENT_DATE)")
    BigDecimal getYearlyIncome();
    
    // 按类型统计收入
    @Query("SELECT i.type, COALESCE(SUM(i.amount), 0) FROM Income i GROUP BY i.type")
    List<Object[]> getIncomeByType();
    
    // 按分类统计收入
    @Query("SELECT i.category, COALESCE(SUM(i.amount), 0) FROM Income i GROUP BY i.category")
    List<Object[]> getIncomeByCategory();
    
    // 按月份统计收入（最近12个月）
    @Query("SELECT YEAR(i.incomeDate), MONTH(i.incomeDate), COALESCE(SUM(i.amount), 0) " +
           "FROM Income i " +
           "WHERE i.incomeDate >= :startDate " +
           "GROUP BY YEAR(i.incomeDate), MONTH(i.incomeDate) " +
           "ORDER BY YEAR(i.incomeDate), MONTH(i.incomeDate)")
    List<Object[]> getMonthlyIncomeStats(@Param("startDate") LocalDateTime startDate);
    
    // 按日期范围查找收入
    @Query("SELECT i FROM Income i WHERE i.incomeDate BETWEEN :startDate AND :endDate")
    List<Income> findByDateRange(String startDate, String endDate);
}
